package com.threeti.mecool.core.application.plan;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;

import com.threeti.mecool.core.domain.model.acl.PermissionRole;
import com.threeti.mecool.core.domain.model.acl.User;
import com.threeti.mecool.core.domain.model.basicdata.AdminDivisionLevelDeep;
import com.threeti.mecool.core.domain.model.plan.PlanJobResult;
import com.threeti.mecool.core.domain.model.project.ProjectStatus;
import com.threeti.mecool.core.infrastructure.persistence.plan.PlanJobResultRepository;
import com.threeti.mecool.core.infrastructure.persistence.project.ProjectEmployeeRepository;
import com.threeti.mecool.framework.util.Lists;

@Service
public class PlanJobResultService {
	
	@Autowired
	PlanJobResultRepository planJobResultRepository;
	
	@Autowired
	JdbcTemplate jdbcTemplate;
	
	@Autowired
	ProjectEmployeeRepository projectEmployeeRepository;
	
	public List<PlanJobResult> findByUserPermissionAndProjectOwnedAndDiv(User user,AdminDivisionLevelDeep level,String divCode) {
		
		
		
		StringBuffer sql=new StringBuffer(" select t.id from DM_WF_PLAN_JOB_RESULT t ");
		
		
		if(AdminDivisionLevelDeep.CITY.equals(level)){
			sql.append(",DM_CHANNEL_SYNC t1 where 1=1 ");
			String divCodeSql=" and t1.channel_city_ad_code = '$DIV_CODE' and t.channel = t1.id ";
			sql.append(divCodeSql.replaceAll("\\$DIV_CODE", divCode));
		}
		
		sql.append("   and t.job_code='WF_JOB_SO' and t.job_status='FINISHED'");
		
		String projectSql=" and t.project_code in ($PROJECT_CODES) "; 
					   
		String areaSql=" and t.channel in (select t.id  "+
					                      " from DM_CHANNEL_SYNC t  "+
					                      " where t.channel_ad_code in  "+
					                      " (select t.managed_admindiv_adcode  "+
					                      " from DM_PERMROLE_ADMIN_DIV_ADCODE t  "+
					                      " where t.permission_role = $ROLE ))";
		
		
		
		
		
		
		PermissionRole permRole = user.getPermissionRole();
		String projectCodesUserParticipatedIn="";
		if (!permRole.isAdvanced()) {
			List<String> projectsUserParticipatedIn = projectEmployeeRepository.findOwnProjectIds(user.getEmployee().getEmpId());
			if (!CollectionUtils.isEmpty(projectsUserParticipatedIn)){
				StringBuffer buffer = new StringBuffer();
				for (String projectCode : projectsUserParticipatedIn) {
			      	buffer.append("," + "'" +projectCode + "'");
			  	}
				projectCodesUserParticipatedIn= buffer.toString().replaceFirst(",", "");
			}else{
				projectCodesUserParticipatedIn="";
			}
			if (permRole.getUseADFilter() == 1) {
				sql.append(projectSql);
				sql.append(areaSql);
			} else {
				sql.append(projectSql);
			}
		} else {
			sql.append(projectSql);
		}
		
		String querySql=sql.toString().replaceAll("\\$PROJECT_CODES", projectCodesUserParticipatedIn)
						  			  .replaceAll("\\$ROLE", permRole.getId().toString());
		
		List<Long> idList = jdbcTemplate.queryForList(querySql, Long.class);
		if(idList.size()<1){
			return Lists.newArrayList();
		}
		return planJobResultRepository.findByIds(idList);
	}
	
}
